 
alter proc sp_pos_sales_everyday_rpt 

@compCode nvarchar(10),
@date nvarchar(10),
@shop nvarchar(3000),
@styleType nvarchar(3000),
@seasonParam nvarchar(3000),
@brandParam nvarchar(3000),
@user_code nvarchar(20),
@emp_no nvarchar(20),
@lang_code nchar(1) = 'A',
@styleNo nvarchar(3000),
@catalog nvarchar(3000),
@ageGroup nvarchar(3000)


as  
SET NOCOUNT ON
begin  


declare @start int, @end int, @ttl_style_count int, @total_text nvarchar(20),@sql_where nvarchar(4000), @sql_part nvarchar(4000),@sql_str nvarchar(4000)

set @sql_where = ''

select @total_text = rtrim(item_text) from igs_objtext where page_name='pos/report/posDailySalesRpt.rpt' and item_name = 'total' and language = @lang_code
if @total_text is null
	set @total_text = 'Total'


create table #tmp_shop(shop_code nchar(100), primary key (shop_code))
insert into #tmp_shop(shop_code)
	exec sp_pos_user_secu @compCode, @user_code, @emp_no, 'Q', @shop
--style_no
if @styleNo <> '' and @styleNo <> '%'
begin
  exec sp_igs_flt_sql @styleNo, 's.style_no', @sql_part out
  set @sql_where = @sql_where + ' and ' + @sql_part
end

--style type
if @styleType <> '' and @styleType <> '%'
begin
  exec sp_igs_flt_sql @styleType, 'tp.style_type', @sql_part out
  set @sql_where = @sql_where + ' and exists (select * from ms_style_type tp where ' + @sql_part + ' and rtrim(tc.type_tree) + ''.'' like rtrim(tp.type_tree) + ''.%'')'
end

--brand
if @brandParam <> '' and @brandParam <> '%'
begin
  exec sp_igs_flt_sql @brandParam, 's.brand', @sql_part out
  set @sql_where = @sql_where + ' and ' + @sql_part
end

--season
if @seasonParam <> '' and @seasonParam <> '%'
begin
  exec sp_igs_flt_sql @seasonParam, 's.season_code', @sql_part out
  set @sql_where = @sql_where + ' and ' + @sql_part
end

--catalog
if @catalog <> '' and @catalog <> '%'
begin
  exec sp_igs_flt_sql @catalog, 's.catalog', @sql_part out
  set @sql_where = @sql_where + ' and ' + @sql_part
end

--age_group
if @ageGroup <> '' and @ageGroup <> '%'
begin
  exec sp_igs_flt_sql @ageGroup, 's.age_group', @sql_part out
  set @sql_where = @sql_where + ' and ' + @sql_part
end

--sales through
--begin tran
declare @sales_month nchar(10)
set @sales_month = @date

create table #temp_days (days int, date_fr datetime, date_to datetime)

--all days temp table
declare @date_fr datetime, @month_to datetime, @next_date datetime
set @date_fr = convert(datetime, substring(@sales_month, 1, 8) + '01')
set @month_to = dateadd(month, 1, @date_fr)
while @date_fr < @month_to
begin
  set @next_date = dateadd(day, 1, @date_fr)
  insert into #temp_days (days, date_fr, date_to) values (datepart(day, @date_fr), @date_fr, @next_date)
  set @date_fr = @next_date
end

--sales data
create table #temp_sales
 (days int, trx_no nchar(100), season_code nchar(20), brand nchar(20), style_no nchar(20), sales_qty int, sales_amt numeric(14,2))

set @sql_str = '
	insert into #temp_sales
	select t.days, h.trx_no, isnull(s.season_code,'''') season_code, 
	case when isnull(s.brand,'''')='''' then ''-'' else rtrim(s.brand) end brand,
	s.style_no,
	sum(h.qty) sales_qty, sum(h.net_amt) as sales_amt
	from v_pos_shop_sales h
	join  pos_sku k on h.sku = k.sku
	join #tmp_shop ts on h.shop_code = ts.shop_code
	join  ms_style s on  k.style_no = s.style_no
	join #temp_days t on h.trx_date >= t.date_fr and h.trx_date < t.date_to 
	join ms_style_type tc on tc.style_type = s.style_type
    where h.trx_type in (''N'', ''R'', ''A'', ''E'') '

if @sql_where <> ''
  set @sql_str = @sql_str + @sql_where

set @sql_str = @sql_str + ' group by t.days, h.trx_no, s.style_no, s.season_code, s.brand'
set @sql_str = @sql_str + ' order by t.days, h.trx_no, s.style_no, s.season_code, s.brand'

exec (@sql_str)
select @ttl_style_count = isnull(count(distinct style_no),0) from #temp_sales

--sales qty, amt group by days
select days, sum(sales_amt) as sales_amt, count(distinct trx_no) as sales_count, sum(sales_qty) as sales_qty,
       case when count(distinct trx_no) = 0 then 0 else sum(sales_amt) / count(distinct trx_no) end as amt_per_trx,
       case when sum(sales_qty) = 0 then 0 else sum(sales_amt) / sum(sales_qty) end as amt_per_qty,
       count(distinct style_no) as style_count
  into #temp_sum
  from #temp_sales group by days


--total row
insert into #temp_sum
	(days, sales_amt, sales_count, sales_qty, amt_per_trx, amt_per_qty, style_count)
	select 32, sum(sales_amt), count(distinct trx_no), sum(sales_qty), 0, 0, @ttl_style_count from #temp_sales

insert into #temp_sales
	(days, trx_no, style_no, season_code, brand, sales_qty, sales_amt)
	select 32, '', '', season_code, brand, sum(sales_qty), sum(sales_amt) from #temp_sales group by season_code, brand

insert into #temp_days (days, date_fr, date_to) values (32, @date_fr, @date_fr)


--return result
select d.days, case when d.days = 32 then @total_text else convert(nvarchar(10), d.days) end as days_str,
       d.date_fr, s.sales_amt, s.sales_count, s.sales_qty, isnull(s.style_count,0) as style_count,
       s.amt_per_trx, s.amt_per_qty, t.season_code, t.brand, sum(t.sales_amt) as sb_amt
into #tmp_res
  from #temp_days d left join #temp_sales t on d.days = t.days
       left join #temp_sum s on d.days = s.days
 group by d.days, d.date_fr, s.sales_amt, s.sales_count, s.sales_qty, s.amt_per_trx, s.amt_per_qty, t.season_code, t.brand, s.style_count
-- order by d.days, t.season_code, t.brand
--rollback tran

declare @seasonCode nvarchar(10)
declare @brand nvarchar(10)

select top 1 @seasonCode = season_code ,@brand = brand from #tmp_res where season_code is not null and brand is not null

update #tmp_res set season_code = @seasonCode, brand = @brand,sb_amt = 0.0 where season_code  is null and brand is null
update #tmp_res set sales_amt = 0 where sales_amt is null
update #tmp_res set sales_count = 0 where sales_count is null
update #tmp_res set sales_qty = 0 where sales_qty is null
update #tmp_res set amt_per_trx = 0 where amt_per_trx is null
update #tmp_res set amt_per_qty = 0 where amt_per_qty is null


select * from #tmp_res



end


go

exec sp_pos_sales_everyday_rpt_test  N'KOYO',N'2012/10/01',N'HKHC,',N'%',N'%',N'%',N'igsadmin',N'',N'B',N'%',N'%',N'%'
